Broadly speaking, database management systems are a branch of tools used by developers to create, maintain, and delete multiple databases. By streamlining the process of database creation, these systems enable developers a quick and user-friendly way of creating databases, specifying subsets of data through tables, and manipulating the data within each.
They also come with many quality-of-life benefits such as:
Automatic indexing which updates after the addition of a new item and frameworks for working with multiple tables at once,Referential integrity assurance, which ensures all indexes are logged and linked if the same data appears multiple times across different tables,The ability to combine data from various separate tables for completion of a single user request.Intro to SQL Tables #Another important aspect to understanding MySQL is to become familiar with Structural Query Language Tables (SQL Tables), the underlying component of any MySQL program.
SQL Tables are a way of storing data in digital tables. These are organized into rigidly defined columns that determines the type and size of all data within that column. The criteria of these columns is as a vital part of any database’s schema.
This SQL Table system used by MySQL and many other relational database management systems available today.
SQL Table databases are an example of a relational database. The table columns organize data based on their share attributes and relationship to other data points.
For more information about database design, see our recent article on Database Design or our article on NoSql Database Queries.
Breakdown of mySQL Parts #Like other relational database management systems (RDBMS), every aspect of MySQL is broken into tiers based on relationships.
Each tier gets more and more specific, allowing developers to finely tune each subset’s accessibility throughout the rest of the program.
mySQL Server Layer#The core of MySQL is the MySQL server. The server is accessible as a separate library that can be embedded into your applications to handle database commands.
All aspects of a MySQL program interact with the MySQL server in some way.
Database#This is the highest and broadest of our tiers, acting as a container for all tiers below it. It is a structured set of data, without which MySQL would not function. Multiple databases can be present in any given program, allowing developers to include widely separate subsets of tables within the same program.
For example, one household could create a single MySQL program containing separate databases for each member, organizing all later data tables to pertain to their defined family member.
Tables#Our second largest tier, tables are the storage locations for related data subsets. Multiple tables can exist under a single database, with one table per user-defined category. Queries must specify in which table they are working, ensuring accidental crossover or prolonged runtime. This is also helpful for privacy, as sometimes we may want to print one of our tables, but keep the other hidden for the current operation.
Continuing our example, each family member database in our household program may have one table for requested birthday gifts, TABLE GIFTS, and another for private passwords, TABLE PASSWORDS.
Columns#Columns further break down tables as each defined column includes a data type, which all values within that column must be. Each column in a table can be a different data type, and further splits the data into contextual subsets. Columns are especially helpful for specifying what information all rows in the table should have (even if it’s just NULL) and storing that row’s value separate from all other rows.
For example, our previous table GIFTS could have three columns: one which lists the product name, ProductName; one which lists the product seller, ProductSeller; and the other which lists the price, Price. The first two of these columns would contain string values while the latter would contain a numeral value.
Row#Rows are the smallest tier and act similar to columns; while columns show a relationship within a type, rows are grouped based on less explicit ways. Each row in a table has a specific key that points to it.
This is best understood through an example. Within GIFTS, we may insert a row, which has determined values for each column: “Mug” for ProductName, “Mug Co.” for ProductSeller, and “15.50” for Price. Without these being on the same row, the user would have no way to see a connection between any of these three variables as they are different values and different types. However, since they are listed under the same row index, it is easy to see that all of these values pertain to the same object.
Query#In MySQL, a query is any command which retrieves data from a table. This is most commonly done through the SELECT command and is the most common type of command used for table operation.